<?php
/**
 * Class represents records from table aff_commission
 * {autogenerated}
 * @property int $commission_id
 * @property int $aff_id
 * @property date $date
 * @property double $amount
 * @property string $record_type commission, void
 * @property int $invoice_id
 * @property int $invoice_payment_id
 * @property int $invoice_item_id
 * @property string $receipt_id
 * @property int $product_id
 * @property bool $is_first
 * @property string $payout_detail_id
 * @property int $tier default: 0
 * @property int $is_voided default: 0
 * @property int $commission_id_void default: 0
 * @see Am_Table
 */

class AffCommission extends Am_Record
{
    const COMMISSION = 'commission';
    const VOID = 'void';

    /** @var Invoice */
    protected $_invoice;
    /** @var User */
    protected $_aff;
    /** @var InvoicePayment */
    protected $_payment;
    /** @var Product */
    protected $_product;

    public function init()
    {
        parent::init();
        $this->record_type = self::COMMISSION;
    }

    public function _setPayment(InvoicePayment $payment=null){ $this->_payment = $payment; return $this; }
    public function _setInvoice(Invoice $invoice){ $this->_invoice = $invoice; return $this; }
    public function _setAff(User $aff){ $this->_aff = $aff; return $this; }
    public function getPayment()
    {
        if (empty($this->invoice_payment_id)) return null;
        return $this->_payment ? $this->_payment : $this->_payment=$this->getDi()->invoicePaymentTable->load($this->invoice_payment_id);
    }
    public function getAff()  { return $this->_aff ? $this->_aff : $this->_aff=$this->getDi()->userTable->load($this->aff_id); }
    public function getInvoice()
    {
        if (empty($this->invoice_id)) return null;
        return $this->_invoice ? $this->_invoice : $this->_invoice=$this->getDi()->invoiceTable->load($this->invoice_id);
    }

    public function getProduct()
    {
        if (empty($this->product_id)) return null;

        return $this->_product ?
            $this->_product :
            $this->_product = $this->getDi()->productTable->load($this->product_id);

    }

    function insert($reload = true)
    {
        $ret = parent::insert($reload);
        $this->getDi()->hook->call(Bootstrap_Aff::AFF_COMMISSION_AFTER_INSERT, [
            'commission' => $this,
            'user'       => $this->getInvoice() ? $this->getInvoice()->getUser() : null,
            'aff'        => $this->getAff(),
            'invoice'    => $this->getInvoice(),
            'payment'    => $this->getPayment(),
        ]);
        return $ret;
    }

    function delete()
    {
        parent::delete();
        $this->deleteFromRelatedTable('?_aff_commission_commission_rule');
    }

    /** param array $rules - array of id# */
    function setCommissionRules(array $rules)
    {
        $this->getAdapter()->query("DELETE FROM ?_aff_commission_commission_rule
            WHERE commission_id=?d {AND rule_id NOT IN (?a)}",
                $this->pk(), $rules ? $rules : DBSIMPLE_SKIP);
        if ($rules) {
            $vals = [];
            foreach ($rules as $id)
                $vals[] = sprintf("(%d,%d)", $this->pk(), $id);
            $this->getAdapter()->query("INSERT IGNORE INTO ?_aff_commission_commission_rule
                (commission_id, rule_id)
                VALUES " . implode(", ", $vals));

            $this->getDi()->hook->call(Bootstrap_Aff::AFF_COMMISSION_COMMISSION_RULE_AFTER_INSERT, [
                'commission' => $this,
                'user'       => $this->getInvoice() ? $this->getInvoice()->getUser() : null,
                'aff'        => $this->getAff(),
                'invoice'    => $this->getInvoice(),
                'payment'    => $this->getPayment(),
            ]);
        }

        return $this;
    }
}

class AffCommissionTable extends Am_Table {
    protected $_key = 'commission_id';
    protected $_table = '?_aff_commission';
    protected $_recordClass = 'AffCommission';

    /**
     * Return query with affiliates linked to sum of their unpaid commissions
     * made up to $toDate
     * @return Am_Query
     */
    function fetchByDate($date, $aff_id=null)
    {
        return $this->selectObjects(
                "SELECT * FROM ?_aff_commission WHERE `date` = ? { AND aff_id=?d}",
                date('Y-m-d', amstrtotime($date)), $aff_id === null ? DBSIMPLE_SKIP : $aff_id);
    }

    function fetchByDateInterval($from, $to, $aff_id=null)
    {
        return $this->selectObjects(
                "SELECT c.*, p.title AS product_title FROM ?_aff_commission c
                    LEFT JOIN ?_product p USING(product_id)
                    WHERE `date` BETWEEN ? AND ? { AND aff_id=?d}",
                date('Y-m-d', amstrtotime($from)), date('Y-m-d', amstrtotime($to)), $aff_id === null ? DBSIMPLE_SKIP : $aff_id);
    }

    /**
     * Return commission stats
     * @param type $startTm
     * @param type $endTm
     * @return array with keys: count and amount
     */
    function getAffStats($aff_id, $startTm, $endTm)
    {
        // return sales count of this affiliate for period
        return $this->_db->selectRow("
            SELECT
                COUNT(DISTINCT(invoice_id)) AS `count`,
                SUM((SELECT COUNT(*) FROM ?_invoice_item WHERE
                    invoice_id=inv.invoice_id AND (first_total>0 OR second_total>0)))
                    AS `items_count`,
                SUM(p.amount) as `amount`
            FROM ?_invoice inv
                INNER JOIN ?_user u USING (user_id)
                LEFT JOIN ?_invoice_payment p USING (invoice_id)
            WHERE u.aff_id=?d
                AND inv.tm_started BETWEEN ? AND ?
        ", $aff_id, sqlTime($startTm), sqlTime($endTm . ' 23:59:59'));
    }

    /*
     * Find last records by invoice_id with the same date (for refunds)
     * @return array AffCommission
     */
    function findLastRecordsByInvoiceId($invoice_id)
    {
        return $this->selectObjects("SELECT * FROM $this->_table
            WHERE
            invoice_id=?d AND record_type='commission' AND
                `date` = (SELECT MAX(`date`) FROM $this->_table WHERE invoice_id=?d)"
            , $invoice_id, $invoice_id);
    }


    function runPayoutForGroup($date, $days, $groups = null, $excludeGroups = null)
    {
        $threseholdDate = sqlDate(amstrtotime($date) - 24 * 3600 * $days);
        // now select all affiliates to pay with one query
        $q = $this->_db->queryResultOnly("
            SELECT 
                SUM(IF(c.record_type=?,-c.amount,c.amount)) AS _total,
                a.*
                {, COUNT(ug.user_user_group_id) AS  _have_groups, ? AS _skip_this}
            FROM 
                ?_aff_commission c RIGHT JOIN ?_user a ON a.user_id=c.aff_id
                {LEFT JOIN ?_user_user_group ug ON a.user_id = ug.user_id AND (ug.user_group_id in (?a))}
            WHERE 
                (c.record_type=? OR c.date<=?) AND (c.payout_detail_id IS NULL)
            GROUP BY c.aff_id
            HAVING 
                _total > 0 
                AND _total >= ?  
                AND a.aff_payout_type IN (?a)
                {AND _have_groups > 0 AND 1=?} 
                {AND _have_groups = 0 AND 1=?} 
        ",
            AffCommission::VOID,
            (empty($groups) && empty($excludeGroups) ? DBSIMPLE_SKIP : '1'),
            (empty($groups) && empty($excludeGroups) ? DBSIMPLE_SKIP : ($groups ?: $excludeGroups)),
            AffCommission::VOID,
            $threseholdDate,
            (double) $this->getDi()->config->get('aff.payout_min', 0),
            $this->getDi()->config->get('aff.payout_methods', [-1]),
            !empty($groups) ? 1 : DBSIMPLE_SKIP,
            !empty($excludeGroups) ? 1 : DBSIMPLE_SKIP
        );
        // then do job
        $payouts = [];
        while ($row = $this->_db->fetchRow($q))
        {
            $aff = $this->getDi()->userTable->createRecord($row);
            if (empty($payouts[$aff->aff_payout_type]))
            {
                $payout = $this->getDi()->affPayoutRecord;
                $payout->type = $aff->aff_payout_type;
                $payout->date = sqlDate($date);
                $payout->thresehold_date = $threseholdDate;
                $payout->insert();
                $payouts[$aff->aff_payout_type] = $payout;
            }
            $detail = $payouts[$aff->aff_payout_type]->addDetail($aff->pk(), $row['_total']);
            $this->_db->query("UPDATE ?_aff_commission c
                SET c.payout_detail_id=?d
                WHERE aff_id=?d AND (c.record_type=? OR c.date<=?) AND (c.payout_detail_id IS NULL)
                ", $detail->pk(), $aff->pk(), AffCommission::VOID, $threseholdDate);
        }
        return $payouts;
    }

    /**
     * Generate payout records for records on $date
     * @param date $date today's date
     */
    function runPayout($date)
    {
        $all_groups = $to_check = [];
        if (($custom_delay = $this->getDi()->config->get('aff.custom_payout_delay')) && !empty($custom_delay))
        {

            foreach ($custom_delay as $gid => $days)
            {
                $days = intval($days);
                $gid = intval($gid);
                if (!$days || !$gid)
                    continue;
                if ($days == $this->getDi()->config->get('aff.payout_delay_days', 30))
                    continue;
                $to_check[$days][] = $gid;
            }

            foreach ($to_check as $days => $groups)
            {
                $all_groups = array_merge($all_groups, $groups);
            }
            $all_groups = array_unique($all_groups);
        }
        $to_check[$this->getDi()->config->get('aff.payout_delay_days', 30)] = null;
        $payouts = [];

        ksort($to_check); // handle the lowest first;

        foreach ($to_check as $days => $groups)
        {
            $payouts = array_merge($payouts, $this->runPayoutForGroup($date, $days, $groups, is_null($groups) ? $all_groups : null));
        }
        foreach ($payouts as $payout)
            $payout->update(); // store totals
        if ($payouts)
        {
            $this->getDi()->hook->call(Bootstrap_Aff::AFF_PAYOUT, [
                'payouts' => $payouts
            ]);
            if ($et = Am_Mail_Template::load('aff.new_payouts'))
                $et->setUrl($this->getDi()->url('aff/admin-payout', null, false, true))->sendAdmin();
        }
    }

    function void(AffCommission $comm, $date = null, $amount = null)
    {
        $void = $this->getDi()->affCommissionRecord;
        $void->fromRow($comm->toRow());
        $void->date = $date ? $date : sqlDate('now');
        $void->commission_id = null;
        $void->payout_detail_id = null;
        $void->record_type = AffCommission::VOID;
        $void->commission_id_void = $comm->pk();
        $void->is_voided = 0;
        if ($amount)
            $void->amount = $amount;
        try
        {
            $void->insert();
            $comm->updateQuick('is_voided', 1);
        }
        catch (Am_Exception_Db_NotUnique $e)
        {
            // already handled?
            if (!$comm->is_voided)
                $comm->updateQuick('is_voided', 1);
        }
    }

    function getStats($start, $stop)
    {
        return (double)$this->_db->selectCell("SELECT SUM(IF(record_type='commission', amount, -1 * amount)) FROM ?_aff_commission
            WHERE date BETWEEN ? AND ?",
            sqlDate($start), sqlDate($stop));
    }

    function selectLast($num)
    {
        return $this->selectObjects(
            "SELECT "
            . "ac.*, aff.login, aff.name_f, aff.name_l, aff.email, "
            . " ip.amount as payment_amount, ip.dattm as dattm, i.public_id, i.user_id "
            . " FROM ?_aff_commission ac "
            . "     LEFT JOIN ?_invoice i USING (invoice_id) "
            . "     LEFT JOIN ?_invoice_payment ip USING (invoice_payment_id) "
            . "     LEFT JOIN ?_user aff ON (aff.user_id=ac.aff_id) "
            . " ORDER BY ac.commission_id DESC "
            . " LIMIT ?d", $num);
    }
}
